﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace DatabaseManagerLib
{
    /// <summary>
    /// Class for management data and some act in database
    /// </summary>
    public sealed class DatabaseManager
    {
        private String _serverName; // name of server where state database
        private String _databaseName; // name of your database
        private String _userName; // user's name for logout to database server
        private String _userPassword; // user's password for logout to database server
        private DataTable _dataTable; // container where storage data of database

        #region ConstructorClass
        private DatabaseManager() { }

        public DatabaseManager(String serverName, String databaseName)
        {
            _serverName = serverName;
            _databaseName = databaseName;
            _userName = "";
            _userPassword = "";
            _dataTable = new DataTable();
        }

        public DatabaseManager(String serverName, String databaseName, String userName)
        {
            _serverName = serverName;
            _databaseName = databaseName;
            _userName = userName;
            _userPassword = "";
            _dataTable = new DataTable();
        }

        public DatabaseManager(String serverName, String databaseName, String userName, String userPassword)
        {
            _serverName = serverName;
            _databaseName = databaseName;
            _userName = userName;
            _userPassword = userPassword;
            _dataTable = new DataTable();
        }
        #endregion

        #region Act with server's database
        /// <summary>
        /// This Method return from database list with login of user
        /// </summary>
        /// <returns>List with logins of clients</returns>
        public List<String> GetListLogin()
        {
            List<String> loginList = new List<String>();
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                loginList.Add(row[tbl.Columns["login"]].ToString().TrimEnd());
            }
            return loginList;
        }

        /// <summary>
        /// Method for connect to server with database
        /// </summary>
        /// <returns>State of connect</returns>
        public String Connect()
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                     + ";Initial Catalog=" + _databaseName
                     + ";Integrated Security=SSPI;";
            String strCommand = "SELECT Login, Password, Name, LastName, BirthDate, Sex FROM ClientInfo;";
            OleDbDataAdapter da = new OleDbDataAdapter(strCommand, strConnect);
            try
            {
                da.Fill(_dataTable);
                return "Connect";
            }
            catch(Exception)
            {
                return "UnConnect";
            }
        }

        /// <summary>
        /// Method create new user in server's database
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <param name="password">Client's login</param>
        public void CreateUser(String login, String password)
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                     + ";Initial Catalog=" + _databaseName
                     + ";Integrated Security=SSPI;";
            String strCommand = "INSERT INTO ClientInfo (Login, Password) VALUES ('" + 
                login + "','" + password + "');";
            SetCommand(strConnect, strCommand);
            _dataTable = new DataTable();
            Connect();
        }

        /// <summary>
        /// Method delete uset from server's database
        /// </summary>
        /// <param name="login">Client's login</param>
        public void DeleteUser(String login)
        {
            
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                 + ";Initial Catalog=" + _databaseName
                 + ";Integrated Security=SSPI;";
            String strCommand = "DELETE FROM ClientInfo WHERE Login = '" + login + "';";
            SetCommand(strConnect, strCommand);
            _dataTable = new DataTable();
            Connect(); // differend
        }

        #endregion

        #region SetFunction

        /// <summary>
        /// Method set Name of client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <param name="name">Client's name</param>
        public void SetName(String login, String name)
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                 + ";Initial Catalog=" + _databaseName
                 + ";Integrated Security=SSPI;";
            String strCommand = "UPDATE ClientInfo SET Name ='" +
                name + "' WHERE Login='" + login + "';";
            SetCommand(strConnect, strCommand);
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    row[tbl.Columns["name"]] = name;
                    break;
                }
            }
            //Connect();
        }

        /// <summary>
        /// Method set LastName of client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <param name="lastName">Client's lastname</param>
        public void SetLastName(String login, String lastName)
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                 + ";Initial Catalog=" + _databaseName
                 + ";Integrated Security=SSPI;";
            String strCommand = "UPDATE ClientInfo SET LastName='" +
                lastName + "' WHERE Login='" + login + "';";
            SetCommand(strConnect, strCommand);
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    row[tbl.Columns["lastname"]] = lastName;
                    break;
                }
            }
            //Connect();
        }

        /// <summary>
        /// Method set BirthDay of client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <param name="birthDay">Client's birthday</param>
        public void SetBirthDay(String login, String birthDay)
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                 + ";Initial Catalog=" + _databaseName
                 + ";Integrated Security=SSPI;";
            String strCommand = "UPDATE ClientInfo SET BirthDate='" +
                birthDay + "' WHERE Login='" + login + "';";
            SetCommand(strConnect, strCommand);
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    row[tbl.Columns["birthdate"]] = birthDay;
                    break;
                }
            }
            //Connect();
        }


        /// <summary>
        /// Method set Sex of client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <param name="sex">Client's sex</param>
        public void SetSex(String login, String sex)
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                 + ";Initial Catalog=" + _databaseName
                 + ";Integrated Security=SSPI;";
            String strCommand = "UPDATE ClientInfo SET Sex='" +
                sex + "' WHERE Login='" + login + "';";
            SetCommand(strConnect, strCommand);
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    row[tbl.Columns["sex"]] = sex;
                    break;
                }
            }
            //Connect();
        }

        /// <summary>
        /// Method set Password of client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <param name="password">Client's password</param>
        public void SetPassword(String login, String password)
        {
            String strConnect = "Provider = SQLOLEDB;Data Source=" + _serverName
                 + ";Initial Catalog=" + _databaseName
                 + ";Integrated Security=SSPI;";
            String strCommand = "UPDATE ClientInfo SET Password='" +
                password + "' WHERE Login='" + login + "';";
            SetCommand(strConnect, strCommand);
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    row[tbl.Columns["password"]] = password;
                    break;
                }
            }
            //Connect();
        }

        /// <summary>
        /// This method create commant for use in server's database
        /// </summary>
        /// <param name="strConnect">Client's login</param>
        /// <param name="strCommand">Command</param>
        private void SetCommand(String strConnect, String strCommand)
        {
            OleDbConnection conn = new OleDbConnection(strConnect);
            OleDbCommand comm = new OleDbCommand(strCommand, conn);
            try
            {
                conn.Open();                
                comm.ExecuteNonQuery();
            }
            catch (Exception) { }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region GetFunction

        /// <summary>
        /// Method get Name of Client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <returns>Name of Client</returns>
        public String GetName(String login)
        {
            String str = "";
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    str = row[tbl.Columns["name"]].ToString().TrimEnd();
                    break;
                }
            }
            return str;
        }

        /// <summary>
        /// Method get LastName of Client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <returns>LastName of Client</returns>
        public String GetLastName(String login)
        {
            String str = "";
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    str = row[tbl.Columns["LastName"]].ToString().TrimEnd();
                    break;
                }
            }
            return str;
        }
        
        /// <summary>
        /// Method get Birthday of Client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <returns>birthday of Client</returns>
        public String GetBirthDay(String login)
        {
            String str = "";
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    str = row[tbl.Columns["BirthDate"]].ToString().TrimEnd();
                    break;
                }
            }
            return str;
        }

        /// <summary>
        /// Method get Sex of Client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <returns>sex of Client</returns>
        public String GetSex(String login)
        {
            String str = "";
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    str = row[tbl.Columns["Sex"]].ToString().TrimEnd();
                    break;
                }
            }
            return str;
        }

        /// <summary>
        /// Method get Password of Client
        /// </summary>
        /// <param name="login">Client's login</param>
        /// <returns>password of Client</returns>
        public String GetPassword(String login)
        {
            String str = "";
            foreach (DataRow row in _dataTable.Rows)
            {
                DataTable tbl = row.Table;
                if (row[tbl.Columns["login"]].ToString().TrimEnd() == login)
                {
                    str = row[tbl.Columns["Password"]].ToString().TrimEnd();
                    break;
                }
            }
            return str;
        }

        #endregion
    }
}
